SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[_PrescriptionDetail_GetRemark]
    @PrescriptionID NVARCHAR(20) ,
    @DrugId NVARCHAR(20)
AS 
    WITH    Data
              AS ( SELECT   DrugId ,
                            DrugName ,
                            Unit ,
                            Quantity ,
                            UnitPrice ,
                            ExpDate ,
                            BatchLot
                   FROM     dbo.PrescriptionDetail
                   WHERE    PrescriptionID = @PrescriptionID
                            AND DrugId = @DrugId
                 )
        SELECT  STUFF(( SELECT  ', ' + BatchLot + ' - '
                                + SUBSTRING(CONVERT(VARCHAR, ExpDate, 102),0,8)
                        FROM    Data data1
                        WHERE   data.DrugId = data1.DrugId
                      FOR
                        XML PATH('')
                      ), 1, 1, '') AS Remark
        FROM    Data data
GO
